<form class="layui-form" method="post">
    <div class="layui-form-item" style="padding-top: 20px;">
        <label class="layui-form-label">excel文件</label>
        <div class="layui-input-inblock">
            <button class="layui-btn layui-btn-small fa fa-upload" id="clickupexcel">上传excel</button>
            <input type="file" id="excelfile"  lay-verify="required" style="display: none" />
            <button onclick="downloadExl(jsono)" id="createexcel">下载excel范例</button>
            <a href="" download="用户信息库范例.xlsx" target="_blank" id="hf"></a>
        </div>
    </div>

    <div class="layui-form-item">
        <div class="layui-input-block">
            <button class="layui-btn" lay-submit lay-filter="remarkexceladd">立即提交</button>
            <button type="reset" class="layui-btn layui-btn-primary">重置</button>
        </div>
    </div>
</form>
<script>
    var postexceldata;
    var siteid=0;
    var exceltypeid=3;
    var jsono = [{ //测试数据
        "cardid": "/'140603197712099411",
        "cardname":"郁亘",
        "privce": "山西省",
        "city":"朔州市",
        "district":"平鲁区"

    }];
    form.render();
    form.on('select(addsitetype)',function(data){
        siteid=data.value;
    });

    form.verify({
        teamname: function(value){
            if(!new RegExp("^$|^[a-zA-Z0-9_\u4e00-\u9fa5\\s·]+$").test(value)){
                return '团队名称不能有特殊字符';
            }else if(/(^\_)|(\__)|(\_+$)/.test(value)){
                return '用户名首尾不能出现下划线\'_\'';
            }else if(/^\d+\d+\d$/.test(value)){
                return '用户名不能全为数字';
            }
            return;
        },
        username: function(value){
            if(!new RegExp("^$|^[^(and|or|exec|insert|select|union|update|&&|count|*|%)]*$").test(value)){
                return '用户名不能有特殊字符';
            }else if(/(^\_)|(\__)|(\_+$)/.test(value)){
                return '用户名首尾不能出现下划线\'_\'';
            }
            return;
        },
        regPwd: function(value){
            var pwd = $("#pwd").val();
            if(!new RegExp(pwd).test(value)) {
                return '两次输入的密码不一致';
            }
            return;
        },
        pass: [
            /^[\S]{6,24}$/
            ,'密码必须6到24位，且不能出现空格'
        ],
    });
    form.on('submit(remarkexceladd)', function(data){
        var userInfo = data.field;
        userInfo.data=postexceldata;
        var url = "__SELF__";
        loadsconrent=layer.load(1, {shade: [0.8, '#393D49'],time: 1000,area: ['100px','100px']});
        $.ajax({
            type: "POST",
            url: url,
            data: userInfo,
            async: false,
            success: function (date) {
                if (date['code'] == 101) {
                    layer.msg(date.msg, {
                        icon: 6,
                        time: 2000
                    }, function(){
                        location.reload();
                    });
                }else{
                    layer.close(loadsconrent)
                    layer.msg(date.msg,{icon: 5});
                }
            }
        });

        return false;
    });

    $('#clickupexcel').on('click',function(){
       $('#excelfile').click();
        return false;
    });

    var wb;//读取完成的数据
    var rABS = false; //是否将文件读取为二进制字符串
    $('#excelfile').on('change',function(){
        var filepath = $(this).val();
        var extStart = filepath.lastIndexOf(".");
        var ext = filepath.substring(extStart, filepath.length).toUpperCase();
        if (ext.toLowerCase() != ".xls".toLowerCase() && ext.toLowerCase() != ".xlsx".toLowerCase() && ext.toLowerCase() != ".xlsm".toLowerCase()) {
            layer.msg("文件仅限于excel表格文件",{icon: 5});
            $(this).text("")
            $(this).val("");
        } else {
            var obj = $(this).context;
            if (!obj.files) {
                return;
            }
            console.log(obj);
            console.log(obj.valueOf)
            var f = obj.files[0];
            var reader = new FileReader();
            reader.onload = function (e) {
                var data = e.target.result;
                if (rABS) {
                    wb = XLSX.read(btoa(fixdata(data)), {type: 'base64'});
                } else {
                    wb = XLSX.read(data, {
                        type: 'binary'
                    });
                }
              //  console.log(to_json(wb.Sheets[wb.SheetNames[0]]));
                //console.log(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));
                //wb.SheetNames[0]是获取Sheets中第一个Sheet的名字
                //wb.Sheets[Sheet名]获取第一个Sheet的数据
               // to_json(wb.Sheets[wb.SheetNames[0]]);
                document.getElementById("demo").innerHTML = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));
                postexceldata = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));



            };
            if (rABS) {
                reader.readAsArrayBuffer(f);
            } else {
                reader.readAsBinaryString(f);
            }
        }
    });


    /*function to_json(workbook) {
        var result = {};
        console.log(workbook);
        layui.each(workbook,function(k,v){
            if(/^[a-zA-Z][a-zA-Z0-9]+$/.test(k)){
                if(eval(k.match(/\d+/g).join('+'))==1){
                    result[v.v]={};
                }else{

                }
              // result[v]
            }
        })
        return result;
    }*/

    function fixdata(data) { //文件流转BinaryString
        var o = "",
            l = 0,
            w = 10240;
        for(; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
        o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
        return o;
    }

</script>
<script>
    function downloadExl(json, type){
        var href ="__PUBLIC__/DEMO.xlsx"; //创建对象超链接
        document.getElementById("hf").href = href; //绑定a标签
        document.getElementById("hf").click(); //模拟点击实现下载
        return false;
    }
    var tmpDown; //导出的二进制对象
    function downloadExlbak(json, type) {
        var tmpdata = json[0];
        json.unshift({});
        var keyMap = []; //获取keys
        for (var k in tmpdata) {
            keyMap.push(k);
            json[0][k] = k;
        }
        var tmpdata = [];//用来保存转换好的json
        json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
            v: v[k],
            position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
        }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
            v: v.v
        });
        var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
        var tmpWB = {
            SheetNames: ['mySheet'], //保存的表标题
            Sheets: {
                'mySheet': Object.assign({},
                    tmpdata, //内容
                    {
                        '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                    })
            }
        };
        tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
            {bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}//这里的数据是用来定义导出的格式类型
        ))], {
            type: ""
        }); //创建二进制对象写入转换好的字节流
        var href = URL.createObjectURL(tmpDown); //创建对象超链接
        document.getElementById("hf").href = href; //绑定a标签
        document.getElementById("hf").click(); //模拟点击实现下载
        setTimeout(function() { //延时释放
            URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
        }, 100);
    }

    function s2ab(s) { //字符串转字符流
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }
    // 将指定的自然数转换为26进制表示。映射关系：[0-25] -> [A-Z]。
    function getCharCol(n) {
        let temCol = '',
            s = '',
            m = 0
        while (n > 0) {
            m = n % 26 + 1
            s = String.fromCharCode(m + 64) + s
            n = (n - m) / 26
        }
        return s
    }
</script>